﻿using Dapper;
using Microsoft.Data.Sqlite;
using System;
using System.CodeDom.Compiler;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WpfNet8Test.Model;

namespace WpfNet8Test.XCDBTool
{
    internal class XCSqliteTool
    {

        //指定数据库文件的存放位置
        //private readonly string ConnectionString = ZStatic.XCStatic.GetConnectionString();

        //不如直接放在项目运行目录
        private const string ConnectionString = "Data Source = mydatabase.db";

        //启用连接池，不过小项目一般是用不到的
        //private const string ConnectionString = "Data Source = mydatabase.db;Pooling=True";

        public void InitializeDatabase()
        {

            using (var connection = new SqliteConnection(ConnectionString))
            {
                connection.Open();

                var command = connection.CreateCommand();
                command.CommandText = @"Create TABLE IF NOT EXISTS Customers(Id INTEGER PRIMARY KEY AUTOINCREMENT,Name TEXT NOT NULL,Email TEXT,CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP )";
                command.ExecuteNonQuery();
            }
        }

        //下面实现CRUD(Create Read Update Delete)操作

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="customer"></param>
        public void AddCustomer(Customer customer)
        {
            using (var connection = new SqliteConnection(ConnectionString))
            {
                connection.Open();
                var command = connection.CreateCommand();
                command.CommandText = @"
                    INSERT INTO Customers(Name,Email)
                    VALUES($name,$email)";

                command.Parameters.AddWithValue("$name", customer.Name);
                command.Parameters.AddWithValue("$email", customer.Email);

                command.ExecuteNonQuery();
            }
        }

        public List<Customer> GetAllCustomers()
        {
            var customers = new List<Customer>();

            using (var connection = new SqliteConnection(ConnectionString))
            {
                connection.Open();

                var command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM Customers";

                using (var reader  = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        customers.Add(new Customer
                        {
                            Id = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            Email = reader.IsDBNull(2)?null:reader.GetString(2),
                            CreatedAt = reader.GetDateTime(3)
                        });
                    }
                    return customers;
                }
            }
        }

        //异步获取所有消费者
        public async Task<List<Customer>> GetAllCustomersAsync()
        {
            var customers = new List<Customer>();

            await using (var connection = new SqliteConnection(ConnectionString))
            {
                await connection.OpenAsync();
                var command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM Customers";

                await using (var reader = await command.ExecuteReaderAsync())
                {
                    while(await reader.ReadAsync())
                    {
                        customers.Add(new Customer
                        {
                            Id = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            Email = reader.IsDBNull(2) ?null:reader.GetString(2),
                            CreatedAt= reader.GetDateTime(3)
                        });
                    }
                }
            }

            return customers;
        }

        public IEnumerable<Customer> GetCustomersWithDapper()
        {
            using (var connection  = new SqliteConnection(ConnectionString))
            {
                return connection.Query<Customer>("SELECT * FROM Customers");
            }
        }

        //添加记录的事务写法
        public void AddCustomerByTransaction(Customer customer)
        {
            using (var connection = new SqliteConnection(ConnectionString))
            {
                connection.Open();

                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        var command = connection.CreateCommand();

                        command.Transaction = transaction;

                        command.CommandText = @"
                         INSERT INTO Customers(Name,Email)
                        VALUES($name,$email)";

                        command.Parameters.AddWithValue("$name", customer.Name);
                        command.Parameters.AddWithValue("$email", customer.Email);

                        command.ExecuteNonQuery();

                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
                    
            }
        }
    }
}
